package com.maxd.respository.helpTask;

import com.maxd.model.HelpTask;
import com.maxd.model.PublisherOrderDtoBean;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.data.repository.Repository;
import org.springframework.transaction.annotation.Transactional;

import java.util.Map;

public interface HelpTaskRepository extends Repository<HelpTask, Long> {

    @Transactional
    @Modifying
    @Query(value = "update help_task t set t.state=:state where t.id=:id",nativeQuery = true)
    Integer updateHelpTaskState(@Param("state") Integer state, @Param("id") Long id);

    @Transactional
    @Modifying
    @Query(value = "update help_task t set t.end_num=t.end_num+1 where t.id=:id",nativeQuery = true)
    Integer updateHelpTaskNum(@Param("id") Long id);

    @Query(value = "select * from help_task t where t.id=:id",nativeQuery = true)
    HelpTask selectById( @Param("id") Long id);

    @Query(value = "select sum(task_original_price) from help_task where state in (1,2) and date_format(create_time,'%Y-%m-%d')=date_format(:time,'%Y-%m-%d')" ,nativeQuery = true)
    Double sumPrice1(String time);

    @Query(value = "select sum(task_original_price) from help_task where state in (1,2) and date_format(create_time,'%Y-%m')=date_format(:time,'%Y-%m')" ,nativeQuery = true)
    Double sumPrice2(String time);

    @Query(value = "select sum(task_original_price) from help_task where state in (1,2) and date_format(create_time,'%Y')=date_format(:time,'%Y')" ,nativeQuery = true)
    Double sumPrice3(String time);

    @Query(value = "select sum(t.task_original_price-s.picture) from help_send_order s " +
            "left join help_task t on s.help_task_id=t.id " +
            "where s.state=2 and date_format(s.create_time,'%Y-%m-%d')=date_format(:time,'%Y-%m-%d')" ,nativeQuery = true)
    Double sumMoneyByProfit1(String time);

    @Query(value = "select sum(t.task_original_price-s.picture) from help_send_order s " +
            "left join help_task t on s.help_task_id=t.id " +
            "where s.state=2 and date_format(s.create_time,'%Y-%m')=date_format(:time,'%Y-%m')" ,nativeQuery = true)
    Double sumMoneyByProfit2(String time);

    @Query(value = "select sum(t.task_original_price-s.picture) from help_send_order s " +
            "left join help_task t on s.help_task_id=t.id " +
            "where s.state=2 and date_format(s.create_time,'%Y')=date_format(:time,'%Y')" ,nativeQuery = true)
    Double sumMoneyByProfit3(String time);

    @Query(value = "select count(*) from help_task where state in (1,2) and date_format(create_time,'%Y-%m-%d')=date_format(:time,'%Y-%m-%d')" ,nativeQuery = true)
    int countHelpTaskByCreateTime1(String time);

    @Query(value = "select count(*) from help_task where state in (1,2) and date_format(create_time,'%Y-%m')=date_format(:time,'%Y-%m')" ,nativeQuery = true)
    int countHelpTaskByCreateTime2(String time);

    @Query(value = "select count(*) from help_task where state in (1,2) and date_format(create_time,'%Y')=date_format(:time,'%Y')" ,nativeQuery = true)
    int countHelpTaskByCreateTime3(String time);

    @Query(value = "select * from help_task t where t.classify_details_id=:classifyDetailsId and t.state=1",
            countQuery = "select count(*) from help_task t where t.classify_details_id=:classifyDetailsId and t.state=1"
            ,nativeQuery = true)
    Page<HelpTask> selectByClassifyDetailsId( @Param("classifyDetailsId") Long classifyDetailsId,Pageable pageable);

    @Query(value = "select * from help_task t where t.classify_details_id in (select id from help_classify_details d where d.classify_deatils_name like CONCAT('%',:title,'%')  ) and t.state=1 ",
            countQuery = "select count(*) from help_task t where t.classify_details_id in (select id from help_classify_details d where d.classify_deatils_name like CONCAT('%',:title,'%')  ) and t.state=1 "
            ,nativeQuery = true)
    Page<HelpTask> selectByClassifyDetailsTitle( @Param("title") String title,Pageable pageable);

    @Query(value = "select * from help_task t where t.title like CONCAT('%',:title,'%') and t.state=1",
            countQuery = "select count(*) from help_task t where t.title like CONCAT('%',:title,'%') and t.state=1"
            ,nativeQuery = true)
    Page<HelpTask> selectByTitle( @Param("title") String title,Pageable pageable);

    @Query(value = "select t.id,t.title,t.classify_details_id as classifyDetailsId,t.content,t.task_price as taskPrice,t.task_num as taskNum,t.end_num as endNum,t.label,t.open_type as openType," +
                    "t.open_content as openContent,t.open_app as openApp,t.verify_content as verifyContent,t.create_time as createTime,t.audit_time as auditTime,t.restrict_time as restrictTime,t.end_time as endTime,ta.state" +
                    " from help_task t " +
                    "left join help_take_order ta on ta.help_task_id=t.id " +
                    "where ta.user_id=:userId and ta.state=:state order by t.create_time desc ",
                    countQuery="select count(*) from help_task t " +
                            "left join help_take_order ta on ta.help_task_id=t.id " +
                            "where ta.user_id=:userId and ta.state=:state order by t.create_time desc ",
                    nativeQuery = true)
    Page<Map<String,Object>> selectMyHelpTask( @Param("userId") Long userId,@Param("state")Integer state,Pageable pageable);

    @Query(value = "select t.id,t.title,t.classify_details_id as classifyDetailsId,t.content,t.task_price as taskPrice,t.task_num as taskNum,t.end_num as endNum,t.label,t.open_type as openType," +
                    "t.open_content as openContent,t.open_app as openApp,t.verify_content as verifyContent,t.create_time as createTime,t.audit_time as auditTime,t.restrict_time as restrictTime,t.end_time as endTime,ta.state" +
                    " from help_task t " +
                    "left join help_take_order ta on ta.help_task_id=t.id " +
                    "where ta.user_id=:userId  order by t.create_time desc ",
                    countQuery="select count(*) from help_task t " +
                            "left join help_take_order ta on ta.help_task_id=t.id " +
                            "where ta.user_id=:userId  order by t.create_time desc ",
                    nativeQuery = true)
    Page<Map<String,Object>> selectMyHelpTask(@Param("userId") Long userId, Pageable pageable);



    @Query(value = "select t.id,t.title,t.classify_details_id as classifyDetailsId,t.content,t.task_price as taskPrice,t.task_num as taskNum,t.end_num as endNum,t.label,t.open_type as openType," +
            "t.open_content as openContent,t.open_app as openApp,t.verify_content as verifyContent,t.create_time as createTime,t.audit_time as auditTime,t.restrict_time  as restrictTime,t.end_time as endTime,ta.state" +
            " from help_task t " +
            "left join help_send_order ta on ta.help_task_id=t.id " +
            "where ta.user_id=:userId and ta.state=:state order by t.create_time desc ",
            countQuery="select count(*) from help_task t " +
                    "left join help_send_order ta on ta.help_task_id=t.id " +
                    "where ta.user_id=:userId and ta.state=:state order by t.create_time desc ",
            nativeQuery = true)
    Page<Map<String,Object>> selectParticipationHelpTask( @Param("userId") Long id,@Param("state")Integer state,Pageable pageable);

    @Query(value = "select t.id,t.title,t.classify_details_id as classifyDetailsId,t.content,t.task_price as taskPrice,t.task_num as taskNum,t.end_num as endNum,t.label,t.open_type as openType," +
            "t.open_content as openContent,t.open_app as openApp,t.verify_content as verifyContent,t.create_time as createTime,t.audit_time as auditTime,t.restrict_time as restrictTime,t.end_time as endTime,ta.state" +
            " from help_task t " +
            "left join help_send_order ta on ta.help_task_id=t.id " +
            "where ta.user_id=:userId  order by t.create_time desc ",
            countQuery="select count(*) from help_task t " +
                    "left join help_send_order ta on ta.help_task_id=t.id " +
                    "where ta.user_id=:userId  order by t.create_time desc ",
            nativeQuery = true)
    Page<Map<String,Object>> selectParticipationHelpTask( @Param("userId") Long id,Pageable pageable);



    @Query(value = "select t.id,t.title,t.classify_details_id as classifyDetailsId,t.content,t.task_price as taskPrice,t.task_num as taskNum,t.end_num as endNum,t.label,t.open_type as openType," +
            "t.open_content as openContent,t.open_app as openApp,t.verify_content as verifyContent,t.create_time as createTime,t.audit_time as auditTime,t.restrict_time as restrictTime,t.end_time as endTime,o.state,o.user_id as userId " +
            " from help_send_order o left join help_task t on t.id=o.help_task_id where o.help_task_id in (select help_task_id from help_take_order where user_id=:userId) and o.state=:state group by o.id ",
            countQuery="select count(*) from help_send_order o left join help_task t on t.id=o.help_task_id where o.help_task_id in (select help_task_id from help_take_order where user_id=:userId) and o.state=:state group by o.id ",
            nativeQuery = true)
    Page<Map<String,Object>> selectAuditHelpTask( @Param("state") Integer state,@Param("userId") Long id,Pageable pageable);

    @Query(value = "select t.id,t.title,t.classify_details_id as classifyDetailsId,t.content,t.task_price as taskPrice,t.task_num as taskNum,t.end_num as endNum,t.label,t.open_type as openType," +
            "t.open_content as openContent,t.open_app as openApp,t.verify_content as verifyContent,t.create_time as createTime,t.audit_time as auditTime,t.restrict_time as restrictTime,t.end_time as endTime,o.state,o.user_id as userId " +
            " from help_send_order o left join help_task t on t.id=o.help_task_id where o.help_task_id in (select help_task_id from help_take_order where user_id=:userId) group by o.id ",
            countQuery="select count(*) from help_send_order o left join help_task t on t.id=o.help_task_id where o.help_task_id in (select help_task_id from help_take_order where user_id=:userId) group by o.id ",
            nativeQuery = true)
    Page<Map<String,Object>> selectAuditHelpTask(@Param("userId") Long id,Pageable pageable);

    @Query(value = "select * from help_task where state=:state",
            countQuery="select count(*) from help_task where state=:state",
            nativeQuery = true)
    Page<HelpTask> selectHelpTaskList( @Param("state") Integer state,Pageable pageable);

    @Query(value = "select t.* from help_task t " +
            "left join help_take_order o on t.id=o.help_task_id " +
            "left join user_info u on u.id=o.user_id " +
            " where 1=1 and if(:phone!='',u.phone=:phone,1=1) and if(:state!=-1 , t.state=:state,1=1)",
            countQuery="select count(*) from help_task  t " +
                    "left join help_take_order o on t.id=o.help_task_id " +
                    "left join user_info u on u.id=o.user_id " +
                    "where if(:phone!='',u.phone=:phone,1=1) and if(:state!=-1 , t.state=:state,1=1)",
            nativeQuery = true)
    Page<HelpTask> selectHelpTaskLists( @Param("state") Integer state,@Param("phone") String phone,Pageable pageable);

    @Query(value = "select * from help_task",
            countQuery="select count(*) from help_task ",
            nativeQuery = true)
    Page<HelpTask> selectHelpTaskList(Pageable pageable);


}
